package controllers;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lib.UtilsInput;
import models.Reporte3;

import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import play.db.DB;
import play.mvc.Controller;

public class Reporte_3 extends Controller {
	
	public static void Reporte_3() {
		if(Security.isAuthenticated())
			render();
		else
			Application.login();
    }
	
	public static void getReporte3(String from,String to){
		String query = "SELECT sol_servicio servicio,SUM(web) webs,SUM(locales) locales FROM (SELECT COUNT(*) AS web,0 locales,sol_servicio FROM sigpc_solicitud WHERE sol_via_solic = 1 GROUP BY sol_servicio UNION SELECT 0 web,COUNT(*) AS locales,sol_servicio FROM sigpc_solicitud WHERE sol_via_solic = 0 GROUP BY sol_servicio) GROUP BY sol_servicio";
		String str = null;
		Gson gson = new Gson();
    	Connection conn = DB.getConnection();
    	
    	//Lista de objetos 
    	List<Reporte3> listReporte3 = new ArrayList<Reporte3>();
    	System.out.println(from);
    	
    	try {
    		if (from != null &&  to != null)
    		{
    			if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase(""))
        			query = "SELECT sol_servicio servicio,SUM(web) webs,SUM(locales) locales from (SELECT COUNT(*) AS web,0 locales,sol_servicio FROM sigpc_solicitud WHERE SOL_FECHA between '" + from + "' AND '" + to + "' and sol_via_solic = 1 GROUP BY sol_servicio UNION SELECT 0 web,COUNT(*) AS locales,sol_servicio FROM sigpc_solicitud WHERE SOL_FECHA between '" + from + "' AND '" + to + "' and sol_via_solic = 0 GROUP BY sol_servicio) GROUP BY sol_servicio";
    		}	
    			
    		
        	ResultSet rs = conn.createStatement().executeQuery(query);
        	
        	 while (rs.next()) {
        		 Reporte3 reporte3 = new Reporte3(rs.getString("servicio"),rs.getInt("webs"),rs.getInt("locales"));
        		 listReporte3.add(reporte3);  	
        	 }
    		
        	 str = "{\"success\":true ,\"records\":" + listReporte3.size()
     				+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte3)
     				+ "}";

     		renderJSON(str);
     		
        	        	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT
	public static void  download_report(String from, String to, String formato) {
	    Map reportParams = new HashMap();
	    
	   if (formato != null)
	   {
	  reportParams.put("from", from);
	  reportParams.put("to",  to);
	    
	  if (formato.equalsIgnoreCase("html"))
		renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_3", reportParams,formato)));
		
	  else
		  renderBinary(lib.BaseJasperReport.generateReport("reporte_3", reportParams,formato), "reporte_3."+ formato);
	   
	   }
	  }


}
